package com.cdp.common.utils;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.*;

public class ExcelUtils {
    public static List<Map<String, Object>> readExcel(String filePath) throws IOException {
        List<Map<String, Object>> data = new ArrayList<>();
        try (FileInputStream fis = new FileInputStream(new File(filePath));
             Workbook workbook = new XSSFWorkbook(fis)) {
            Sheet sheet = workbook.getSheetAt(0);
            Row headerRow = sheet.getRow(0);
            int columnCount = headerRow.getLastCellNum();

            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                Map<String, Object> rowData = new HashMap<>();
                for (int j = 0; j < columnCount; j++) {
                    Cell cell = row.getCell(j);
                    String header = headerRow.getCell(j).getStringCellValue();
                    if (cell != null) {
                        switch (cell.getCellType()) {
                            case STRING:
                                rowData.put(header, cell.getStringCellValue());
                                break;
                            case NUMERIC:
                                if (DateUtil.isCellDateFormatted(cell)) {
                                    rowData.put(header, cell.getDateCellValue());
                                } else {
                                    rowData.put(header, cell.getNumericCellValue());
                                }
                                break;
                            case BOOLEAN:
                                rowData.put(header, cell.getBooleanCellValue());
                                break;
                            default:
                                rowData.put(header, null);
                        }
                    } else {
                        rowData.put(header, null);
                    }
                }
                data.add(rowData);
            }
        }
        return data;
    }



    public static List<Map<String, Object>> readExcelV2(String filePath) throws IOException {
        List<Map<String, Object>> data = new ArrayList<>();
        try (FileInputStream fis = new FileInputStream(new File(filePath));
             Workbook workbook = new XSSFWorkbook(fis)) {

            // 读取 Sheet2（索引为 1）
            Sheet sheet = workbook.getSheetAt(1);
            Row headerRow = sheet.getRow(0);
            int columnCount = headerRow.getLastCellNum();

            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }

                Map<String, Object> rowData = new LinkedHashMap<>(); // 保持列顺序
                for (int j = 0; j < columnCount; j++) {
                    Cell cell = row.getCell(j);
                    String header = headerRow.getCell(j).getStringCellValue();

                    if (cell != null) {
                        switch (cell.getCellType()) {
                            case STRING:
                                rowData.put(header, cell.getStringCellValue());
                                break;
                            case NUMERIC:
                                if (DateUtil.isCellDateFormatted(cell)) {
                                    rowData.put(header, cell.getDateCellValue());
                                } else {
                                    rowData.put(header, cell.getNumericCellValue());
                                }
                                break;
                            case BOOLEAN:
                                rowData.put(header, cell.getBooleanCellValue());
                                break;
                            default:
                                rowData.put(header, null);
                        }
                    } else {
                        rowData.put(header, null);
                    }
                }
                data.add(rowData);
            }
        }
        return data;
    }


//    public static List<Map<String, Object>> readExcelV2(String filePath) throws IOException {
//        List<Map<String, Object>> data = new ArrayList<>();
//        try (FileInputStream fis = new FileInputStream(new File(filePath));
//             Workbook workbook = new XSSFWorkbook(fis)) {
//            Sheet sheet = workbook.getSheetAt(0);
//            Row headerRow = sheet.getRow(0);
//            int columnCount = headerRow.getLastCellNum();
//
//            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
//                Row row = sheet.getRow(i);
//                Map<String, Object> rowData = new HashMap<>();
//                for (int j = 0; j < columnCount; j++) {
//                    Cell cell = row.getCell(j);
//                    String header = headerRow.getCell(j).getStringCellValue();
//                    if (cell != null) {
//                        switch (cell.getCellType()) {
//                            case STRING:
//                                rowData.put(header, cell.getStringCellValue());
//                                break;
//                            case NUMERIC:
//                                if (DateUtil.isCellDateFormatted(cell)) {
//                                    rowData.put(header, cell.getDateCellValue());
//                                } else {
//                                    rowData.put(header, cell.getNumericCellValue());
//                                }
//                                break;
//                            case BOOLEAN:
//                                rowData.put(header, cell.getBooleanCellValue());
//                                break;
//                            default:
//                                rowData.put(header, null);
//                        }
//                    } else {
//                        rowData.put(header, null);
//                    }
//                }
//                data.add(rowData);
//            }
//        }
//        return data;
//    }
}